#!/usr/bin/env python
# Copyright (c) 2014 Eugene Frolov <efrolov@mirantis.com>
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#    http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
# implied.
# See the License for the specific language governing permissions and
# limitations under the License.

import re
import time
import math
import xml.etree.ElementTree as ET

import _mysql

from rdb.old_app import config
from rdb.old_app import mysql_class
from rdb.old_app import query_class


class engine(mysql_class.MySQL_class, config.template):

    msgByErrCode = ['OK', 'Ошибка подключения к Базе данных.',
                    'Ошибка загрузки темплейта.']

    def __init__(self):
        mysql_class.MySQL_class.__init__(self)

    def getTemplate(self, tmplName):
        try:
            f = open(self.tmplName[tmplName], 'r')
            buf = f.read()
            f.close()
            return buf
        except:
            self.ErrCode = 2

    def CreateDataTRN(self, tmpl, data):
        prog = re.compile('<%(msplist_(\w+?)_(\w+))%>')
        return prog.findall(tmpl)

    def GenXML(self, query_result, names_data):
        root = ET.Element("main")
        index0 = 0
        index1 = 0
        nameIndex = 0
        element0 = []
        element1 = []
        for res in query_result:
            for i in res:
                element0.append(ET.SubElement(root, names_data[nameIndex]))
                index0 += 1
                for j in i:
                    element1.append(ET.SubElement(element0[index0 - 1], j))
                    if i[j] is None:
                        i[j] = ""
                    element1[index1].text = i[j].decode('UTF-8')
                    index1 += 1
            nameIndex += 1
        return "<?xml version='1.0' encoding='utf-8' ?>" + ET.tostring(root)

    def GetXMLMSPTable(self):
        resp = self.query(query_class.query_msptable)
        res1 = resp.store_result().fetch_row(how=1, maxrows=0)

        resp = self.query(query_class.query_unitlist)
        self.trace('newh ' + str(resp))
        res2 = resp.store_result().fetch_row(how=1, maxrows=0)

        resp = self.query(query_class.query_currencylist)
        self.trace('newh ' + str(resp))
        res3 = resp.store_result().fetch_row(how=1, maxrows=0)

        resp = self.query(query_class.query_modellist)
        self.trace('newh ' + str(resp))
        res4 = resp.store_result().fetch_row(how=1, maxrows=0)

        resp = self.query(query_class.query_model_swaplist)
        self.trace('newh ' + str(resp))
        res5 = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res1, res2, res3, res4, res5],
                           ['msp', 'unit', 'currency', 'model', 'model_swap'])

    def GetXMLUnitList(self):
        resp = self.query(query_class.query_unitlist)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['unit'])

    def GetXMLCurrencyList(self):
        resp = self.query(query_class.query_currencylist)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['currency'])

    def GetXMLModelList(self):
        resp = self.query(query_class.query_modellist)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['model'])

    def addMSPRecord(self, dataline):
        if (dataline is not None):
            try:
                self.query(query_class.query_addmsp % (
                    str(dataline['code']), str(dataline['description']),
                    str(dataline['unit']), str(dataline['description_rus']),
                    str(dataline['integer']), str(dataline['code']),
                    str(dataline['safety_stock']),
                    str(dataline['planning_type']),
                    str(dataline['planning_condition'])
                ))

                for i in dataline['model']:
                    self.query(query_class.query_addmodel_swap % (
                        dataline['code'], i))
            except:
                self.query(query_class.query_addmspupdate % (
                    str(dataline['description']), str(dataline['unit']),
                    str(dataline['description_rus']), dataline['integer'],
                    str(dataline['code']), str(dataline['safety_stock']),
                    str(dataline['planning_type']),
                    str(dataline['planning_condition']),
                    str(dataline['code'])
                ))

                self.query(query_class.query_clearswaplistbyid % str(
                    dataline['code']))

                for i in dataline['model']:
                    self.query(query_class.query_addmodel_swap % (
                        dataline['code'], i))

            return dataline

    def addUnit(self, name):
        if (name is not None):
            self.query(query_class.query_addUnit % (name))

            return 0

    def addCurrency(self, name):
        if (name is not None):
            self.query(query_class.query_addCurrency % (name))

            return 0

    def addModel(self, name):
        if (name is not None):
            self.query(query_class.query_addModel % (name))

            return 0

    def test1(self):
        resp = self.query("SELECT `description_rus` FROM `spare_parts`")
        res = resp.store_result().fetch_row(
            how=1, maxrows=1)[0]['description_rus']

        return res

    def getMSPTable(self):
        resp = self.query(query_class.query_msptable)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return res

    def getUnitById(self, id):
        resp = self.query(query_class.query_unitbyid % id)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['descr']

        return res

    def getCurrencyById(self, id):
        resp = self.query(query_class.query_currencybyid % id)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['currency']

        return res

    def getModelsById(self, id):
        resp = self.query(query_class.query_modelsbyid % id)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return res

    def GetCSV(self, TableName):
        if TableName == 'MSP':
            tmpCSV = str(config.csvStringChar + "code" + config.csvStringChar +
                         config.csvSplitChar +
                         config.csvStringChar + "description" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "description rus" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "unit" + config.csvStringChar +
                         config.csvSplitChar +
                         config.csvStringChar + "integer?" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "safety stock" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "planning type" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "planning condition" +
                         config.csvStringChar + config.csvSplitChar +
                         config.csvStringChar + "model" +
                         config.csvStringChar +
                         '\n')
            for msp in self.getMSPTable():
                tmpCSV += (config.csvStringChar + msp['code'] +
                           config.csvStringChar + config.csvSplitChar)
                tmpCSV += (config.csvStringChar + msp['description'] +
                           config.csvStringChar + config.csvSplitChar)
                tmpCSV += (config.csvStringChar +
                           msp['description_rus'].decode('utf-8')
                           .encode('cp1251') +
                           config.csvStringChar + config.csvSplitChar)
                tmpCSV += (config.csvStringChar +
                           self.getUnitById(msp['unit']) +
                           config.csvStringChar +
                           config.csvSplitChar)
                if int(msp['integer']) == 0:
                    tmpCSV += (config.csvStringChar + 'no' +
                               config.csvStringChar + config.csvSplitChar)
                else:
                    tmpCSV += (config.csvStringChar + 'yes' +
                               config.csvStringChar + config.csvSplitChar)

                tmpCSV += (config.csvStringChar + msp['safety_stock'] +
                           config.csvStringChar + config.csvSplitChar)
                if msp['planning_type'] == "0":
                    tmpCSV += (config.csvStringChar + 'MANUAL' +
                               config.csvStringChar + config.csvSplitChar)
                else:
                    tmpCSV += (config.csvStringChar + 'SYSTEM' +
                               config.csvStringChar + config.csvSplitChar)
                tmpCSV += (config.csvStringChar + msp['planning_condition'] +
                           config.csvStringChar + config.csvSplitChar)
                modellist = ""
                for model in self.getModelsById(msp['model']):
                    modellist += model['name'] + ','
                tmpCSV += (config.csvStringChar + modellist[:-1] +
                           config.csvStringChar)
                tmpCSV += '\n'
                self.SaveFileToTemp('generated.csv', tmpCSV)
            return ('<a href="/tmp/generated.csv">'
                    'Click here to download file</a>')

    def SaveFileToTemp(self, filename, data):
        fd = open('/var/www/rdb/tmp/' + filename, "wb")
        fd.write(data)
        fd.close()

    def getIdByUnit(self, name):
        resp = self.query(query_class.query_idbyunit % name)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:
            return None

    def getIdByCurrency(self, name):
        resp = self.query(query_class.query_idbycurrency % name)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:
            return None

    def getIdByModel(self, name):
        resp = self.query(query_class.query_idbymodel % name)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:
            return None

    def CheckMSPLineCSV(self, csvline):
        if self.getIdByUnit(csvline[3]) is None:
            return csvline[3]
            exit()
        for name in csvline[8].split(","):
            if self.getIdByModel(name) is None:
                return name
                exit()
        return 0

    def ConvertToMSPLine(self, csvline):
        postdate = {}
        postdate['code'] = csvline[0]
        postdate['description'] = csvline[1]
        postdate['description_rus'] = (
            csvline[2].decode("cp1251").encode("utf-8"))
        if csvline[4].upper() == 'NO':
            csvline[4] = '0'
        else:
            csvline[4] = '1'
        postdate['integer'] = csvline[4]
        newline = []
        for name in csvline[8].split(","):
            newline.append(self.getIdByModel(name))
        postdate['model'] = newline
        if csvline[6].upper() == "MANUAL":
            postdate['planning_type'] = '0'
        else:
            postdate['planning_type'] = '1'
        postdate['safety_stock'] = csvline[5]
        postdate['unit'] = str(self.getIdByUnit(csvline[3]))
        postdate['planning_condition'] = csvline[7]
        return postdate

    def setMSPCSV(self, csvdata):
        if csvdata[0][0] == 'code':
            del csvdata[0]
        errmsg = ""
        for csvline in csvdata:
            res = self.CheckMSPLineCSV(csvline)
            if res == 0:
                self.addMSPRecord(self.ConvertToMSPLine(csvline))
            else:
                errmsg += "Error. Item '" + res + "' not found.\n"
                return errmsg
        return ("Ready!!!<script language='javascript'>"
                "parent.update_api.update('mspdialog');</script>")

    def getMSPlineByID(self, id):
        resp = self.query(query_class.query_msplinebyid % id)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]

            resp = self.query(query_class.query_imodelbysid % res['model'])
            res2 = resp.store_result().fetch_row(how=0, maxrows=0)

            res['model'] = []
            for id in res2:
                res['model'].append(id[0])
            return res
        except:
            return None

    def getSizeDialog(self, userId, dialogName):
        resp = self.query(query_class.query_getSizeDialog % (
            userId, dialogName))
        res = resp.store_result().fetch_row(how=1, maxrows=1)

        if res == ():
            resp = self.query(query_class.query_insertZerroSize % (
                userId, dialogName))

            resp = self.query(query_class.query_getSizeDialog % (
                userId, dialogName))
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]

            return res
        else:
            return res[0]

    def setDialogSize(self, userId, dialogName, left, top, width, height):
        self.query(query_class.query_updateSize % (
            left, top, width, height, userId, dialogName))

        return 'Ok'

    def getMRTAI(self):
        resp = self.query(query_class.query_autoRepairCode)
        res = resp.store_result().fetch_row(
            how=1, maxrows=1)[0]['Auto_increment']

        return res

    def GetXMLSPCode(self):
        resp = self.query(query_class.query_spcode)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['spcode'])

    def GetXMLSPCodeTable(self, guid):
        resp = self.query(query_class.query_spcodetable % guid)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['spcodetable'])

    def GetXMLSPCodeTableFull(self, id):
        resp = self.query(query_class.query_spcodetablefull % id)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['spcodetable'])

    def getIsIntegerBySPCode(self, spcode):
        resp = self.query(query_class.query_getIsIntegerBySPCode % spcode)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['integer']

        return res

    def addSPCode(self, guid, spcode, quantity):
        isInt = self.getIsIntegerBySPCode(spcode)
        quantity = quantity.replace(",", ".")
        if isInt == '1':
            quantity = math.ceil(float(quantity))
        self.query(query_class.query_addSPCode % (guid, spcode, quantity))

        return "spcode = " + spcode, "isInteger = " + isInt, "guid = " + guid

    def delSPCodeByID(self, id):
        self.query(query_class.query_delSPCodeByID % id)

        return "ok"

    def getRTGUIDByRTcode(self, code):
        resp = self.query(query_class.query_getRTGUIDByRTCode % code)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['GUID']

            return res
        except:
            return None

    def postmrt(self, postdata):
        self.query(query_class.query_delmrtmodel % postdata['guid'])
        for i in postdata['model']:
            self.query(query_class.query_addmrtmodel % (postdata['guid'], i))
        if self.getRTGUIDByRTcode(postdata['code']) != postdata['guid']:
            postdata['repairTypePrice'] = postdata['repairTypePrice'].replace(
                ',', '.')
            postdata['repairPartsPrice'] = postdata[
                'repairPartsPrice'].replace(',', '.')
            self.query(query_class.query_addRepairType % (
                postdata['repairType'],
                postdata['repairTypeRus'],
                postdata['guid'],
                postdata['guid'],
                postdata['repairTypePrice'],
                postdata['repairPartsPrice'],
                postdata['currency'],
                postdata['customer']
            ))
        else:
            self.query(query_class.query_updateRTRecord % (
                postdata['currency'],
                postdata['repairPartsPrice'],
                postdata['repairTypePrice'],
                postdata['guid'],
                postdata['guid'],
                postdata['repairTypeRus'],
                postdata['repairType'],
                postdata['customer'],
                postdata['code']
            ))
        return self.getRTGUIDByRTcode(postdata['code']), postdata['code']

    def getCustomerGroup(self):
        resp = self.query(query_class.query_customerGroup)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['customergroup'])

    def addCustomerGroup(self, group):
        self.query(query_class.query_addCustomerGroup % (group))

        return "OK"

    def addCustomer(self, name, group):
        self.query(query_class.query_addCustomer % (name, group))

        return "OK"

    def addDestination(self, daddr):
        self.query(query_class.query_addDestination % (daddr))

        return "OK"

    def getRepairType(self):
        resp = self.query(query_class.query_getRepairType)
        res1 = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res1], ['repairtype'])

    def getRepairTypeFilter(self, customer, model):
        resp = self.query(
            query_class.query_getRepairTypeFilter % (customer, model))
        res1 = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res1], ['repairtype'])

    def getCustomer(self):
        resp = self.query(query_class.query_getCustomer)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['customer'])

    def getDModel(self):
        resp = self.query(query_class.query_getDModel)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['dmodel'])

    def getDDestination(self):
        resp = self.query(query_class.query_getDDestination)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['ddestination'])

    def getTransactionType(self):
        resp = self.query(query_class.query_getTransactionType)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['transaction'])

    def addTransactionType(self, type, formula):
        self.query(query_class.query_addTransactionType % (
            type, formula))

        return "OK"

    def getATI(self):
        resp = self.query(query_class.query_autoTransactionType)
        res = resp.store_result().fetch_row(
            how=1, maxrows=1)[0]['Auto_increment']

        return res

    def getSPIntegerLi(self, spcode):
        resp = self.query(query_class.query_addSPIntegerLi % spcode)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['integer']

        return res

    def addInventoryAdjustment(self, spcode, transactiontype, quantity, date):
        datemas = date.split('-')
        if (len(datemas) != 3):
            date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
        else:
            date = '%s-%s-%s' % (datemas[2], datemas[1], datemas[0])
        quantity = quantity.replace(',', '.')
        if (self.getSPIntegerLi(spcode) == "1"):
            quantity = quantity.split('.')[0]
        self.query(query_class.query_addInventoryAdjustment % (
            spcode, transactiontype, quantity, date))

        return "OK"

    def convertDate(self, date):
        tmpDate = date.split("-")
        return "%s-%s-%s" % (tmpDate[2], tmpDate[1], tmpDate[0])

    def getTransactionByDate(self, fromdate, todate, fromitem, toitem,
                             fromtype, totype, detail):
        if (fromdate == ''):
            fromdate = '1987-01-01'
        else:
            fromdate = self.convertDate(fromdate)
        if (todate == ''):
            todate = '9999-12-31'
        else:
            todate = self.convertDate(todate)
        if (fromitem == ""):
            fromitem = "0"
        if (toitem == ""):
            toitem = "9999999999"
        if (fromtype == ""):
            fromtype = "0"
        if (totype == ""):
            totype = "9999999999"
        if detail == "false":
            resp = self.query(query_class.query_getTransactionByDate % (
                fromdate, todate, fromitem, toitem, fromtype, totype))
            res = resp.store_result().fetch_row(how=1, maxrows=0)

        else:
            resp = self.query(query_class.query_getTransactionByDateDetail % (
                fromdate, todate, fromitem, toitem, fromtype, totype))
            res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['transaction'])

    def getWarehouseInventoryByDate(self, date, zerro, safety):
        if (date == ''):
            date = '9999-12-31'
        else:
            date = self.convertDate(date)
        if (zerro == 'true'):
            zerro = -9999999
            resp = self.query(
                query_class.query_getWarehouseInventoryByDate0 % (
                    date, date, zerro))
            res = resp.store_result().fetch_row(how=1, maxrows=0)

        else:
            zerro = 1
            if (safety == 'true'):
                resp = self.query(
                    query_class.query_getWarehouseInventoryByDate1 % (
                        date, date, '-9999999'))
                res = resp.store_result().fetch_row(how=1, maxrows=0)

            else:
                resp = self.query(
                    query_class.query_getWarehouseInventoryByDate1 % (
                        date, date, zerro))
                res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['inventory'])

    def getWarehouseInventoryByItem(self, date, spcode):
        if (date == ''):
            date = '9999-12-31'
        else:
            date = self.convertDate(date)
        resp = self.query(
            query_class.query_getWarehouseInventoryByItem % (
                date, date, spcode))
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['inventory'])

    def getZeroQuantity(self):
        resp = self.query(query_class.query_getZeroQuantity)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['inventory'])

    def getReapairType(self):
        resp = self.query(query_class.query_getReapairType)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['repairtype'])

    def getCurrentDate(self):
        dateNow = time.localtime()
        return "%d-%d-%d" % (dateNow[0], dateNow[1], dateNow[2])

    def getGUIDByRCode(self, rCode):
        query = ("SELECT  `repairtype` as `GUID`  FROM  `repair_note` WHERE "
                 " `repairnumber` = '%s'" % rCode)
        resp = self.query(query)
        try:
            return resp.store_result().fetch_row(how=1, maxrows=1)[0]['GUID']
        except IndexError:
            return 'None'

    def setRepairNote(self, postdata):

        if self.getGUIDByRCode(postdata['rnumber']) != postdata['GUID']:
            postdata['rnumber'] = self.getRepairNumber()

        if postdata['rdate'] != '':
            postdata['rdate'] = self.convertDate(postdata['rdate'])
        else:
            postdata['rdate'] = self.getCurrentDate()
        if postdata['ddate'] != 'NULL':
            postdata['ddate'] = '"' + self.convertDate(postdata['ddate']) + '"'
        try:
            postdata['cooler'] = int(postdata['cooler'])
        except:
            pass
        try:
            resp = self.query(query_class.query_setReapairNote % (
                postdata['rnumber'], postdata['cooler'],
                postdata['rdate'], postdata['brand'],
                postdata['ddate'], postdata['dnote'],
                postdata['snote'], postdata['rtype'],
                postdata['transactiontype'], postdata['GUID']
            ))

        except:
            if (type(postdata['cooler']) == int)and(postdata['cooler'] > 0):
                resp = self.query(query_class.query_setReapairNoteUpdate % (
                    postdata['cooler'], postdata['rdate'],
                    postdata['brand'], postdata['ddate'],
                    postdata['dnote'], postdata['snote'],
                    postdata['rtype'], postdata['transactiontype'],
                    postdata['GUID'], postdata['rnumber']
                ))
            else:
                resp = self.query(query_class.query_setReapairNoteUpdateFix % (
                    postdata['rdate'],
                    postdata['brand'], postdata['ddate'],
                    postdata['dnote'], postdata['snote'],
                    postdata['rtype'], postdata['transactiontype'],
                    postdata['GUID'], postdata['rnumber']
                ))

        # del all transaction by transactionhash
        resp = self.query(
            query_class.query_delAllTransactionByHash % postdata['GUID'])

        resp = self.query(
            query_class.query_getSparePartsByGUID % postdata['GUID'])
        res = list(resp.store_result().fetch_row(how=1, maxrows=0))

        resp = self.query(query_class.query_getListRTbyGUID % postdata['GUID'])
        listRT = list(resp.store_result().fetch_row(how=1, maxrows=0))

        if len(listRT) != 0:
            for lineRT in listRT:
                resp = self.query(
                    query_class.query_getAutoTransactionRTL % lineRT['rtcode'])
                res += list(resp.store_result().fetch_row(how=1, maxrows=0))

        if len(res) != 0:
            for line in res:
                resp = self.query(
                    query_class.query_insertAutoTransactionOSP % (
                        line['spcode'], postdata['transactiontype'],
                        line['quantity'], postdata['rdate'],
                        postdata['GUID']))

        return res, listRT

    def getRepairNumber(self):
        resp = self.query(query_class.query_getRepairNumber)
        res = resp.store_result().fetch_row(
            how=1, maxrows=1)[0]['Auto_increment']

        return "R" + "0" * (10 - len(res)) + res

    def getConsumptionRepairCode(self):
        resp = self.query(query_class.query_getConsumptionRepairCode)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

        return res

    def getRepairNoteTable(self):
        resp = self.query(query_class.query_getRepairNoteTable)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['repairnote'])

    def getRepairItem(self, id):
        resp = self.query(query_class.query_getRepairItem % id)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]

        if res['specialnote'] is not None:
            res['specialnote'] = res['specialnote'].decode('utf-8')
        else:
            res['specialnote'] = ""
        if res['dispatchnote'] is not None:
            res['dispatchnote'] = res['dispatchnote'].decode('utf-8')
        else:
            res['dispatchnote'] = ""
        if res['dispatchdate'] is None:
            res['dispatchdate'] = ""
        return res

    def getRepairNoteTableReport(self, param, value):
        if (param != "repairtype"):
            resp = self.query(query_class.query_getRepairNoteTableReport % (
                param, value))
            res = resp.store_result().fetch_row(how=1, maxrows=0)

        else:
            resp = self.query(
                query_class.query_getRepairNoteTableReportRTCode % (value))
            res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['repairnote'])

    def createCSVHeader(self, headerList):
        Res = ""
        for i in headerList:
            Res += (config.csvStringChar + str(i) + config.csvStringChar +
                    config.csvSplitChar)
        return Res[:-1] + '\n'

    helperData = {
        'gprice': {
            'id': ['code', 'description', 'group', 'gprice'],
            'type': {'code': 'string', 'description': 'string',
                     'group': 'string', 'gprice': 'double'},
            'header': ['SP Code *', 'Description', 'Group *',
                       'Price for group *']
        },
        'coolerinventory': {
            'id': [],
            'type': ['string', 'string', 'string', 'string', 'date'],
            'header': ['Customer *', 'Destination *', 'Model *',
                       'Serial Number *', 'Inventory Number *',
                       'Cooler Receipt Date *']
        },
        'sparepartsorder': {
            'id': [],
            'type': ['string', 'string', 'integer', 'date'],
            'header': ['Spare parts Code *', 'Spare Parts Description',
                       'Q-ty to Order *', 'Order Date *']
        },
        'inventoryadjustment': {
            'id': [],
            'type': ['integer', 'integer', 'integer', 'date'],
            'header': ['Item Code *', 'Transaction type *', 'Quantity *',
                       'Transaction Date *']
        }
    }

    def createCSVContent(self, contentList, idList, type):
        ResTotal = ""
        for i in contentList:
            Res = ""
            for j in idList:
                if type[j] == 'double':
                    i[j] = str(i[j]).replace('.', ',')
                Res += (config.csvStringChar + str(i[j]) +
                        config.csvStringChar + config.csvSplitChar)
            ResTotal += Res[:-1] + '\n'
        return ResTotal

    def ConvertToSQL(self, SQLReply, table):
        header = self.helperData[table]['header']
        id = self.helperData[table]['id']
        type = self.helperData[table]['type']
        csv = self.createCSVHeader(header) + self.createCSVContent(
            list(SQLReply), id, type)
        return csv

    def getGroupPricing(self):
        resp = self.query(query_class.query_getGroupPricing)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.ConvertToSQL(res, 'gprice')

    def getGroupPricingZero(self):
        resp = self.query(query_class.query_getGroupPricingZero)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.ConvertToSQL(res, 'gprice')

    def getGroupIdByName(self, name):
        resp = self.query(query_class.query_getGroupIdByName % (name))
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

        return res

    def getGroupPriceLine(self, spcode, groupId):
        resp = self.query(query_class.query_getGroupPriceLine % (
            spcode, groupId))
        res = resp.store_result().fetch_row(how=1, maxrows=1)

        return res

    def updateGroupPrice(self, lineCSV):
        try:
            lineCSV[2] = self.getGroupIdByName(lineCSV[2])
            ERR = 0
        except:
            return "ERROR: Group '" + lineCSV[2] + "' not found."
            ERR = 1
        if ERR == 0:
            lineCSV[3] = lineCSV[3].replace(',', '.')
            if len(self.getGroupPriceLine(lineCSV[0], lineCSV[2])) == 0:
                self.query(query_class.query_insertGroupPrace % (
                    lineCSV[0], lineCSV[2], lineCSV[3]))

            else:
                self.query(query_class.query_updateGroupPrace % (
                    lineCSV[3], lineCSV[0], lineCSV[2]))

            return "OK"

    def getIdByCustomer(self, name):
        resp = self.query(query_class.query_idbycustomer % name)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:

            return None

    def getIdByDestination(self, name):
        resp = self.query(query_class.query_idbydestination % name)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:

            return None

    def insertCoolerInventory(self, lineCSV):
        lineCSV[1] = lineCSV[1].decode("cp1251").encode("utf-8")
        if self.getIdByCustomer(lineCSV[0]) is None:
            return lineCSV[0]
            exit()
        if self.getIdByDestination(lineCSV[1]) is None:
            return lineCSV[1]
            exit()
        if self.getIdByModel(lineCSV[2]) is None:
            return lineCSV[2]
            exit()
        lineCSV[0] = self.getIdByCustomer(lineCSV[0])
        lineCSV[1] = self.getIdByDestination(lineCSV[1])
        lineCSV[2] = self.getIdByModel(lineCSV[2])
        lineCSV[5] = self.convertDate(lineCSV[5].replace('.', '-'))
        try:
            self.query(query_class.query_insertCoolerRecept % (
                lineCSV[0], lineCSV[1], lineCSV[2], lineCSV[3].upper(),
                lineCSV[4], lineCSV[5]))

        except _mysql.IntegrityError:

            self.query(query_class.query_updateCoolerRecept % (
                lineCSV[0], lineCSV[1], lineCSV[2], lineCSV[3].upper(),
                lineCSV[5], lineCSV[4]))

        return 0

    def UploadCSV(self, csv, table):
        if (self.helperData[table]['header'] == csv[0]):
            csv = csv[1:]
            tmp = ""
            for i in csv:
                if table == 'gprice':
                    tmp = self.updateGroupPrice(i)
                elif table == 'coolerinventory':
                    tmp = self.insertCoolerInventory(i)
                    if tmp != 0:
                        tmp += "ERROR: " + tmp + " NOT FOUND!<br />"
                    else:
                        tmp = ""
                elif table == 'sparepartsorder':
                    if self.getIdSPCode(i[0]) is None:
                        tmp += ("ERROR: Spare Parts Code " + i[0] +
                                " NOT FOUND!<br />")
                    else:
                        postdata = {}
                        postdata['spcode'] = self.getIdSPCode(i[0])
                        postdata['quantity'] = i[2]
                        postdata['date'] = i[3]
                        self.addSPOrder(postdata)
                elif table == 'inventoryadjustment':
                    if self.getIdSPCode(i[0]) is None:
                        tmp += ("ERROR: Spare Parts Code " + i[0] +
                                " NOT FOUND!<br />")
                    else:
                        postdata = {}
                        postdata['spcode'] = self.getIdSPCode(i[0])
                        postdata['transactiontype'] = i[1]
                        postdata['quantity'] = i[2].replace(" ", "").replace(
                            ",", ".")
                        postdata['date'] = i[3].replace(".", "-")
                        self.addInventoryAdjustment(
                            postdata['spcode'],
                            postdata['transactiontype'],
                            postdata['quantity'],
                            postdata['date'])
            if tmp[0:5] == "ERROR":
                return tmp
            return "Ready!!!"
        else:
            return "Bad CSV file!"

    def getGPrices(self):
        resp = self.query(query_class.query_getGroupPricing)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['gprice'])

    def getPrintDetailedInvoicebyPeriod(self, fromdate, todate, param):
        if fromdate != '':
            fromdate = self.convertDate(fromdate)
        else:
            fromdate = '0001-01-01'
        if todate != '':
            todate = self.convertDate(todate)
        else:
            todate = fromdate
        resp = self.query(query_class.query_getPrintDetailedInvoicebyPeriod % (
            fromdate, todate, param))
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        res = list(res)
        for i in xrange(len(res) - 1):
            if (res[len(res) - i - 2]['price'] == res[len(res) - i - 1][
                    'price']):
                res[len(res) - i - 1]['price'] = ''
        return self.GenXML([res], ['PDIbP'])

    def getPrintInvoicebyModel(self):
        resp = self.query(query_class.query_getPrintInvoicebyModel)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['PIbM'])

    def getRNote(self):
        resp = self.query(query_class.query_getRNote)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['rnote'])

    def getPrintInvoicebyRepairType(self, rnote):
        resp = self.query(
            query_class.query_getPrintInvoicebyRepairType % (rnote))
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['PIbRT'])

    def getPrintOtherSparePartsperRepairNote(self, param):
        resp = self.query(
            query_class.query_getPrintOtherSparePartsperRepairNote % param)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['POSPpRN'])

    def getPrintRepairNoteNumber(self, rnote):
        resp = self.query(
            query_class.query_getPrintRepairNoteNumber1 % (rnote))
        res1 = resp.store_result().fetch_row(how=1, maxrows=0)

        resp = self.query(
            query_class.query_getPrintRepairNoteNumber2 % (rnote))
        res2 = resp.store_result().fetch_row(how=1, maxrows=0)

        res1 += res2
        return self.GenXML([res1], ['PRNN'])

    def getCoolerInventory(self, param):
        resp = self.query(query_class.query_getCoolerInventory % (param))
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['coolerinventory'])

    def getSPNeedOrder(self):
        resp = self.query(query_class.query_getSPNeedOrder)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['order'])

    def getIdSPCode(self, spcode):
        resp = self.query(query_class.query_idbyspcode % spcode)
        try:
            res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['id']

            return res
        except:

            return None

    def addSPOrder(self, postdata):
        postdata['date'] = self.convertDate(postdata['date'].replace(".", "-"))
        self.query(query_class.query_insertSPOrder % (
            postdata['spcode'], postdata['quantity'], postdata['date']))

        return "OK"

    def getOrderedSPTable(self):
        resp = self.query(query_class.query_getSPNeedOrderTable)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['orderedsp'])

    def getCloseOrderedSPTable(self):
        resp = self.query(query_class.query_getCloseSPNeedOrderTable)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['orderedsp'])

    def CloseOrder(self, qty, id):
        self.query(query_class.query_closeOrderById % (qty, id))

        return "OK"

    def GetXMLRTCodeTable(self, guid):
        resp = self.query(query_class.query_rtcodetable % guid)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['rtcodetable'])

    def addRTCode(self, guid, rtype):
        self.query(query_class.query_addRTCode % (guid, rtype))

        return "ok"

    def delRTCodeByID(self, id):
        self.query(query_class.query_delRTCodeByID % id)

        return "ok"

    def returnCooler(self, id):
        resp = self.query(query_class.query_getDispatchDateById % id)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]['repairdate']

        if res is None:
            resp = self.query(query_class.query_returnCooler % id)

        return res

    def getCoolerSNFromCoolerReceipt(self):
        resp = self.query(query_class.query_getCoolerSNFromCoolerReceipt)
        res = resp.store_result().fetch_row(how=1, maxrows=0)

        return self.GenXML([res], ['snumber'])

    def getCoolerById(self, id):
        resp = self.query(query_class.query_getCoolerById % (id))
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]

        return res

    def getModelRepairCount(self, param):
        resp = self.query(query_class.query_getModelRepairCount % param)
        res = resp.store_result().fetch_row(how=1, maxrows=0)
        total = 0
        for record in res:
            total += int(record['countmodel'])
        res = res + ({'model': 'Total', 'countmodel': str(total)},)

        return self.GenXML([res], ['models'])

    def getMRTItem(self, id):
        resp = self.query(query_class.query_getRTItem % id)
        res = resp.store_result().fetch_row(how=1, maxrows=1)[0]

        res['repairTypeRus'] = res['repairTypeRus'].decode('UTF-8')
        model_resp = self.query(query_class.query_getmrtmodelids %
                                res['model'])
        model_res = model_resp.store_result().fetch_row(how=1, maxrows=0)
        res['model'] = []
        for i in model_res:
            res['model'].append(str(i['modelid']))
        return res

    def getMySQLInfo(self):
        return self.handles
